import os
import sys

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.base.DataField import DataField
from src.db.FCDB import FCDB
from src.db.LocalDb import LocalDb
from src.db.ProdDb import ProdDb
from src.utils import BatchUtils
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger
from src.base.EntityBase import EntityBase
from src.db.CcxDataCenterDb import CcxDataCenter


# announcement表实体类
class Announcement(EntityBase):
    ID = DataField("ID")
    ORIGIN_ID = DataField("ORIGIN_ID")
    DECLAREDATE = DataField("DECLAREDATE")
    ANNTYPE = DataField("ANNTYPE")
    ANNTITLE = DataField("ANNTITLE")
    CREDITCODE = DataField("CREDITCODE")
    COMPNAME = DataField("COMPNAME")
    DATASOUCE = DataField("DATASOUCE")
    FILELINK = DataField("FILELINK")
    TMSTAMP = DataField("TMSTAMP")
    BONDBASEINFOID = DataField("BONDBASEINFOID")
    ISVALID = DataField("ISVALID")
    TYPE = DataField("TYPE")
    BUCKET = DataField("BUCKET")
    FILENAME = DataField("FILENAME")


# 同步公告数据
class SyncAnnouncement:
    def __init__(self, logger: Logger, localDb: LocalDb, fcdb: FCDB, dcdb: CcxDataCenter):
        self.logger = logger
        self.localDb = localDb
        self.fcdb = fcdb
        self.dcdb = dcdb
        self.size = 10000
        self.annType = ['定期报告', '募集公告', '信用级别变动', '推迟评级', '终止评级']

    def __queryTqBdProannouncemt__(self, maxTMSTAMP):
        sql = """
            SELECT ID, SECURITYID,DECLAREDATE,ANNTYPE,ANNTITLE,BONDBASEINFOID,TMSTAMP,DATASOUCE
            FROM fcdb.tq_bd_proannouncemt
            where
            ANNTYPE in %s
            AND TMSTAMP > %s order by TMSTAMP limit 10000;
            """
        if maxTMSTAMP is None:
            maxTMSTAMP = 0
        return self.fcdb.Query(sql, (self.annType, maxTMSTAMP,))

    def __queryTqOaAnntfile__(self, bondBaseInfoIds):
        sql = """
            SELECT RELAID, FILELINK
            FROM tq_oa_anntfile
            WHERE RELAID in %s
            """
        return self.fcdb.Query(sql, (bondBaseInfoIds,))

    def __queryLocalMaxTMSTAMP__(self):
        sql = "SELECT max(TMSTAMP) as TMSTAMP from announcement where TYPE=1"
        r = self.localDb.QueryOne(sql)
        if r is None or len(r) == 0 or r["TMSTAMP"] is None:
            return 0
        return r["TMSTAMP"]

    def __queryIdsByOriginId__(self, dType: int, originIds: list):
        if originIds is None or len(originIds) == 0:
            return []
        sql = """
            select ID, ORIGIN_ID from announcement
            where `TYPE` = %s AND ORIGIN_ID IN %s
        """
        return self.localDb.Query(sql, (dType, originIds))

    def __update__(self, updateDatas):
        sql = """    
        replace INTO announcement
        (ID, ORIGIN_ID, DECLAREDATE, ANNTYPE, ANNTITLE, CREDITCODE, COMPNAME, 
        DATASOUCE, FILELINK, TMSTAMP, BONDBASEINFOID, ISVALID, 
        TYPE, BUCKET, FILENAME)
        VALUES (%(ID)s, %(ORIGIN_ID)s, %(DECLAREDATE)s, %(ANNTYPE)s, %(ANNTITLE)s, %(CREDITCODE)s, %(COMPNAME)s, 
        %(DATASOUCE)s, %(FILELINK)s, %(TMSTAMP)s, %(BONDBASEINFOID)s, %(ISVALID)s,
        %(TYPE)s, %(BUCKET)s,%(FILENAME)s);
        """
        return self.localDb.ExecMany(sql, updateDatas)

    def __querySecurities__(self):
        sql = """
        SELECT security_id, issuer_code from deb_debenture_base
        """
        return self.localDb.Query(sql)

    def __queryEnterprise__(self):
        sql = """
        SELECT comp_code, credit_code, enterprise_name FROM ent_enterprise
        """
        return self.localDb.Query(sql)

    def __tagDuplicate__(self):
        """
        根据 企业，公告标题，监控日期 重复，保留最新的一条
        :return:
        """
        sql = """
            WITH codes AS (
                SELECT distinct CREDITCODE FROM announcement
                WHERE ISVALID = 1 
                GROUP BY CREDITCODE, ANNTITLE, DECLAREDATE
                HAVING COUNT(1) > 1
                LIMIT 100000
            )
            , ids AS  (
            SELECT ID FROM (
            SELECT ID , ROW_NUMBER() over( PARTITION BY CREDITCODE, ANNTITLE, DECLAREDATE ORDER BY ORIGIN_ID DESC) AS no
            FROM announcement
            WHERE ISVALID = 1 
            AND TYPE = 1
            AND CREDITCODE IN (SELECT CREDITCODE FROM codes)
            ) t
                WHERE t.no > 1									 
            )
            UPDATE announcement SET ISVALID = 0
            WHERE id IN (SELECT id FROM ids)
        """
        return self.localDb.ExecNoneQuery(sql)

    def __deleteDuplicate__(self):
        sql = """
        delete from announcement where ISVALID = 0
        """
        return self.localDb.ExecNoneQuery(sql)

    def __querySSEDatas__(self):
        sql = """
            SELECT * FROM sse_pdf WHERE `status` = 1
        """
        return self.dcdb.Query(sql)

    def syncAnnouncement(self):

        # 加载债券
        self.logger.info("加载债券")
        securities = self.__querySecurities__()
        securities = {e["security_id"]: e for e in securities}
        self.logger.info("加载债券 %d" % len(securities))

        # 加载企业
        self.logger.info("加载企业")
        enterpriseList = self.__queryEnterprise__()
        enterpriseList = {e["credit_code"]: e for e in enterpriseList}
        self.logger.info("加载企业 %d" % len(enterpriseList))

        while True:
            maxTMSTAMP = self.__queryLocalMaxTMSTAMP__()
            self.logger.info("同步 announcement maxTMSTAMP=%d" % maxTMSTAMP)
            insertDatas = self.__queryTqBdProannouncemt__(maxTMSTAMP)
            if len(insertDatas) == 0:
                self.logger.info("==============没有更多的数据=================")
                break
            self.logger.info("新增数据 %d" % len(insertDatas))

            # 加载文档ID
            self.logger.info("加载文档数据")
            bondBaseInfoIds = list(set(d["BONDBASEINFOID"] for d in insertDatas if d["BONDBASEINFOID"] is not None))
            anntfiles = BatchUtils.batchQuery(self.__queryTqOaAnntfile__, bondBaseInfoIds, self.size)
            self.logger.info("加载文档数据 %d" % len(anntfiles))
            anntfiles = {d["RELAID"]: d["FILELINK"] for d in anntfiles}

            # 加载本地数据ID
            self.logger.info("加载本地数据ID")
            dataIds = list(d["ID"] for d in insertDatas)
            localIds = self.__queryIdsByOriginId__(1, dataIds)
            localIds = {d["ORIGIN_ID"]: d["ID"] for d in localIds}
            self.logger.info("加载本地数据ID %d" % len(localIds))

            # 格式转换
            updateDatas = []
            for d in insertDatas:
                # 获取债券
                security = None if d["SECURITYID"] not in securities.keys() else securities[d["SECURITYID"]]
                # 根据债券获取主体
                if security is not None:
                    enterprise = None if security["issuer_code"] not in enterpriseList.keys() else enterpriseList[security["issuer_code"]]
                else:
                    enterprise = None

                if enterprise is None:
                    # 非名单内企业，跳过
                    continue

                data = Announcement()
                data.ID = None if d["ID"] not in localIds.keys() else localIds.get(d["ID"])
                data.ORIGIN_ID = d["ID"]
                data.DECLAREDATE = d["DECLAREDATE"]
                data.ANNTYPE = '信用评级公告' if d["ANNTYPE"] == '信用级别变动' else d["ANNTYPE"]
                data.ANNTITLE = d["ANNTITLE"]
                data.CREDITCODE = enterprise["credit_code"]
                data.COMPNAME = enterprise["enterprise_name"]
                data.DATASOUCE = d["DATASOUCE"]
                data.FILELINK = None if d["BONDBASEINFOID"] not in anntfiles.keys() else anntfiles.get(d["BONDBASEINFOID"])
                data.TMSTAMP = d["TMSTAMP"]
                data.BONDBASEINFOID = d["BONDBASEINFOID"]
                data.ISVALID = 1
                data.TYPE = 1
                data.BUCKET = None
                data.FILENAME = None
                updateDatas.append(data)
            updateDatas = list(ent.__dict__ for ent in updateDatas)
            self.logger.info("更新数据库 开始")
            BatchUtils.batchUpdate(self.__update__, updateDatas, self.size)
            self.logger.info("更新数据库 完成")

            # 本次同步无数据
            if len(updateDatas) == 0:
                self.logger.info("实际更新数据为0，处理结束")
                break

    def syncSse(self):

        # 加载SSE数据
        self.logger.info("加载SSE数据")
        sseDatas = self.__querySSEDatas__()
        self.logger.info(f"加载SSE数据 {len(sseDatas)}")

        self.logger.info("加载本地数据ID")
        dataIds = list(d["id"] for d in sseDatas)
        localIds = self.__queryIdsByOriginId__(2, dataIds)
        localIds = {d["ORIGIN_ID"]: d["ID"] for d in localIds}
        self.logger.info("加载本地数据ID %d" % len(localIds))

        updateDatas = []
        for d in sseDatas:
            data = Announcement()
            data.ID = None if d["id"] not in localIds.keys() else localIds.get(d["id"])
            data.ORIGIN_ID = d["id"]
            data.DECLAREDATE = d["publish_date"]
            data.ANNTYPE = d["report_type"]
            data.ANNTITLE = d["title"]
            data.CREDITCODE = d["credit_code"]
            data.COMPNAME = d["enterprise_name"]
            data.DATASOUCE = '10'  # 固定10
            data.FILELINK = None
            data.TMSTAMP = None
            data.BONDBASEINFOID = None
            data.ISVALID = 1
            data.TYPE = 2
            data.BUCKET = d["bucket"]
            data.FILENAME = d["filename"]
            updateDatas.append(data)
        updateDatas = list(ent.__dict__ for ent in updateDatas)

        self.logger.info("更新数据库 开始")
        BatchUtils.batchUpdate(self.__update__, updateDatas, self.size)
        self.logger.info("更新数据库 完成")

    def removeDuplicate(self):

        self.logger.info("清理重复数据 开始")

        while True:

            # 给重复数据打标签
            taggedCnt = self.__tagDuplicate__()
            self.logger.info(f"标记重复数据 {taggedCnt} 条")

            # 删除无效数据
            deleteCnt = self.__deleteDuplicate__()
            self.logger.info(f"删除重复数据 {deleteCnt} 条")

            if taggedCnt == 0 or deleteCnt == 0:
                self.logger.info("没有需要清理的重复数据。")
                break

        self.logger.info("清理重复数据 结束")


# 公告
def main():
    cleanScreen()
    logger = Logger()
    localDb = ProdDb(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)
    dcdb = CcxDataCenter(logSql=False, autocommit=True)

    logger.info("============同步 announcement 开始================")
    sync = SyncAnnouncement(logger=logger, localDb=localDb, fcdb=fcdb, dcdb=dcdb)

    # 同步财汇数据
    sync.syncAnnouncement()

    # 清理重复
    sync.removeDuplicate()

    # 同步sse数据
    sync.syncSse()

    logger.info("============同步 announcement 完成================")


# 程序入口
if __name__ == "__main__":
    main()
